﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace OrmSmackdown.AdoNet
{
    public class Crud : ICrud
    {
        string _connectionString;
        private SqlCommand _cmd;

        public Crud()
        {
            _cmd = new SqlCommand();
            _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
        }

        public int Create(string firstName, string lastName, string email)
        {
            long personId = -1;
            _cmd.Parameters.Clear();

            _cmd.CommandText = @"
insert into People (FirstName, LastName, email, UserType)
values(@firstName, @lastName, @email, @userType);
select SCOPE_IDENTITY();";

            _cmd.Parameters.AddWithValue("@firstName", firstName);
            _cmd.Parameters.AddWithValue("@lastName", lastName);
            _cmd.Parameters.AddWithValue("@email", email);
            _cmd.Parameters.AddWithValue("@userType", "CN");

            using (_cmd.Connection = new SqlConnection(_connectionString))
            {
                _cmd.Connection.Open();
                personId = Convert.ToInt32(_cmd.ExecuteScalar());
            }

            return (int)personId;
        }

        public IPerson Read(int id)
        {
            Person person = null;
            _cmd.Parameters.Clear();

            _cmd.CommandText = "select * from people where PersonId = @personId";
            _cmd.Parameters.AddWithValue("@personId", id);

            using (_cmd.Connection = new SqlConnection(_connectionString))
            {
                _cmd.Connection.Open();
                using (var rdr = _cmd.ExecuteReader())
                {                    
                    if (rdr.Read())
                    {
                        person = new Person();
                        person.Email = rdr["Email"].ToString();
                        person.FirstName = rdr["FirstName"].ToString();
                        person.LastName = rdr["LastName"].ToString();
                        person.AddressList = new List<IAddress>();
                    }
                }
            }

            return person as IPerson;
        }

        public void Update(int id, string firstName, string lastName, string email)
        {
            _cmd.Parameters.Clear();
            _cmd.CommandText = @"
update people 
 set FirstName = @firstName,
     LastName = @lastName,
     Email = @email
 where PersonId = @personId";

            _cmd.Parameters.AddWithValue("@firstName", firstName);
            _cmd.Parameters.AddWithValue("@lastName", lastName);
            _cmd.Parameters.AddWithValue("@email", email);
            _cmd.Parameters.AddWithValue("@personId", id);

            using (_cmd.Connection = new SqlConnection(_connectionString))
            {
                _cmd.Connection.Open();
                _cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int id)
        {
            _cmd.Parameters.Clear();
            _cmd.CommandText = "delete from People where PersonId = @personId";
            _cmd.Parameters.AddWithValue("@personId", id);

            using (_cmd.Connection = new SqlConnection(_connectionString))
            {
                _cmd.Connection.Open();
                _cmd.ExecuteNonQuery();
            }
        }


        public void Dispose()
        {
            _cmd.Dispose();
        }
    }
}
